package inventario;

import conexion.CConexion;
import java.io.IOException;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import validaciones.CLogin;

public class Cconsumo extends CConexion {

    private double totalEgresos;
    private String compania = null;
    private String fechaEgreso = null;

    public Cconsumo() {
        super();
        login = new CLogin();

    }

    public void setTotalEgresos() throws SQLException {
        ResultSet rsTotalEgresos = null;
        Statement stncTotalEgresos = this.getConexion().createStatement();
        String sqlTotalEgresos = "SELECT sum(egr101tota) totalegr from invegr100,invegr101 WHERE egr100nocia='" + compania + "' AND egr100nocia=egr101nocia AND egr100bode =egr101bode AND egr100tido = egr101tido AND egr100nodo = egr101nodo AND egr100fech ='" + fechaEgreso + "'";
        rsTotalEgresos = stncTotalEgresos.executeQuery(sqlTotalEgresos);
        rsTotalEgresos.next();
        totalEgresos = rsTotalEgresos.getDouble(1);
    }

    public void setFechaEgreso(String fechaEgreso) {
        this.fechaEgreso = fechaEgreso;
    }

    public void setNoCia(String noCia) {
        this.compania = noCia;
    }

    public double getTotalEgresos() {
        return totalEgresos;
    }

    public String getNoCia() {
        return compania;
    }

    public String getFechaEgreso() {
        return fechaEgreso;
    }

    public ResultSet cargarEgresos() throws SQLException, IOException, ClassNotFoundException {
        ResultSet rsCargarEgresos = null;
        Statement stncCargarEgresos = this.getConexion().createStatement();
        String sqlCargarEgresos = "SELECT egr100fech,cd100area,sum(egr101tota)totcon from invegr100,invegr101,invcd100 WHERE egr100nocia = cd100nocia AND  egr101nocia=cd100nocia AND cd100nocia='" + compania + "' AND egr100nocia=egr101nocia AND egr100bode =egr101bode AND egr100tido = egr101tido AND egr100nodo = egr101nodo AND egr101cdgo =cd100cdgo AND egr100fech = '" + fechaEgreso + "' GROUP BY  egr100fech,cd100area";
        rsCargarEgresos = stncCargarEgresos.executeQuery(sqlCargarEgresos);
        return rsCargarEgresos;

    }

    public ResultSet buscaEgresos() throws SQLException {
        ResultSet rsBuscaEgresos = null;
        String sqlBuscaEgresos = "SELECT egr100fech,sum(if(cd100area='REA', + egr101tota,0)) as egr100rea,sum(if(cd100area='INS', + egr101tota,0)) as egr100ins,sum(if(cd100area='LAB', + egr101tota,0)) as egr100lab,sum(if(cd100area='AFI', + egr101tota,0)) as egr100afi,sum(if(cd100area='MED', + egr101tota,0)) as egr100med,sum(if(cd100area='OFI', + egr101tota,0))as egr100ofi,sum(if(cd100area='UTI', + egr101tota,0))as egr100uti,(SELECT sum(if(cd100area='REA' ||cd100area='INS'||cd100area='LAB' ||cd100area='AFI' ||cd100area='MED' ||cd100area='OFI' ||cd100area='UTI' ,+egr101tota,+0)) from invegr100,invegr101,invcd100 WHERE egr100nocia = cd100nocia AND  egr101nocia=cd100nocia AND cd100nocia='" + compania + "' AND egr100nocia=egr101nocia AND egr100bode =egr101bode AND egr100tido = egr101tido AND egr100nodo = egr101nodo AND egr101cdgo =cd100cdgo AND egr100fech = '" + fechaEgreso + "') as egr100total from invegr100,invegr101,invcd100 WHERE egr100nocia = cd100nocia AND  egr101nocia=cd100nocia AND cd100nocia='" + compania + "' AND egr100nocia=egr101nocia AND egr100bode =egr101bode AND egr100tido = egr101tido AND egr100nodo = egr101nodo AND egr101cdgo =cd100cdgo AND egr100fech = '" + fechaEgreso + "'";
        Statement stncBuscaEgresos = this.getConexion().createStatement();
        rsBuscaEgresos = stncBuscaEgresos.executeQuery(sqlBuscaEgresos);
        rsBuscaEgresos.next();
        return rsBuscaEgresos;
    }
}
